﻿using DuckDB.NET.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Dapper.Sharding
{
    internal class DuckDbDatabase : IDatabase
    {
        public DuckDbDatabase(string name, IClient client) : base(name, client)
        {
            ConnectionString = ConnectionStringBuilder.BuilderDuckDb(client.Config, name);
        }

        public override string ConnectionString { get; set; }

        public override void DropTable(string name)
        {
            Execute($"DROP TABLE IF EXISTS {name}");
        }

        public override bool ExistsTable(string name)
        {
            return ExecuteScalar<bool>($"SELECT 1 FROM information_schema.tables WHERE table_schema='main' AND table_name='{name}'");
        }

        public override async Task<bool> ExistsTableAsync(string name)
        {
            return await ExecuteScalarAsync<bool>($"SELECT 1 FROM information_schema.tables WHERE table_schema='main' AND table_name='{name}'");
        }

        public override DbConnection GetConn()
        {
            var conn = new DuckDBConnection(ConnectionString);
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch
                {
                    conn.Dispose();
                    throw;
                }
            }
            return conn;
        }

        public override async Task<DbConnection> GetConnAsync()
        {
            var conn = new DuckDBConnection(ConnectionString);
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    await conn.OpenAsync();
                }
                catch
                {
#if CORE
                    await conn.DisposeAsync();
#else
                    conn.Dispose();
#endif
                    throw;
                }
            }
            return conn;
        }

        public override List<string> GetTableColumnList(string name)
        {
            return Query<string>($"SELECT column_name FROM information_schema.columns WHERE table_schema='main' AND table_name='{name}'");
        }

        public override async Task<List<string>> GetTableColumnListAsync(string name)
        {
            return await QueryAsync<string>($"SELECT column_name FROM information_schema.columns WHERE table_schema='main' AND table_name='{name}'");
        }

        public override TableEntity GetTableEntityFromDatabase(string name, bool firstCharToUpper = false)
        {
            var entity = new TableEntity();
            entity.PrimaryKey = "";
            var manager = GetTableManager(name);
            var indexList = manager.GetIndexEntityList();
            entity.IndexList = indexList;
            var ix = indexList.FirstOrDefault(f => f.Type == IndexType.PrimaryKey);
            if (ix != null)
            {
                entity.PrimaryKey = ix.Columns.FirstCharToUpper();
            }
            entity.ColumnList = manager.GetColumnEntityList(entity, firstCharToUpper);

            if (entity.PrimaryKey != null)
            {
                var col = entity.ColumnList.FirstOrDefault(w => w.Name.ToLower() == entity.PrimaryKey.ToLower());
                if (col != null)
                {
                    entity.PrimaryKeyType = col.CsType;
                }
            }
            return entity;
        }

        public override List<string> GetTableList()
        {
            return Query<string>("SELECT table_name FROM information_schema.tables WHERE table_schema='main'");
        }

        public override ITableManager GetTableManager(string name)
        {
            return new DuckDbTableManager(name, this);
        }

        public override string GetTableScript<T>(string name)
        {
            var tableEntity = ClassToTableEntityUtils.Get<T>(DbType, DbVersion);
            var sb = new StringBuilder();
            sb.Append($"CREATE TABLE IF NOT EXISTS {name} (");
            foreach (var item in tableEntity.ColumnList)
            {
                string dbtype = item.DbType;

                if (tableEntity.PrimaryKey.ToLower() == item.Name.ToLower())
                {
                    sb.Append($"{item.Name} {dbtype}");
                    sb.Append(" PRIMARY KEY");
                }
                else
                {
                    sb.Append($"{item.Name} {dbtype}");
#if CORE6
                    if (item.CsType.IsValueType && item.CsType != typeof(DateTime) && item.CsType != typeof(DateTimeOffset) && item.CsType != typeof(DateOnly) && item.CsType != typeof(TimeOnly) && item.CsType != typeof(DateTime?) && item.CsType != typeof(DateTimeOffset?) && item.CsType != typeof(DateOnly?) && item.CsType != typeof(TimeOnly?))
#else
                    if (item.CsType.IsValueType && item.CsType != typeof(DateTime) && item.CsType != typeof(DateTimeOffset) && item.CsType != typeof(DateTime?) && item.CsType != typeof(DateTimeOffset?))
#endif
                    {
                        if (item.CsType != typeof(bool))
                        {
                            if (!item.DbType.ToLower().EndsWith("default 0"))
                            {
                                sb.Append(" DEFAULT 0");
                            }
                        }
                        else
                        {
                            if (!item.DbType.ToLower().EndsWith("default false"))
                            {
                                sb.Append(" DEFAULT FALSE");
                            }
                        }
                    }
                }

                if (item != tableEntity.ColumnList.Last())
                {
                    sb.Append(",");
                }
            }
            sb.Append(")");
            foreach (var ix in tableEntity.IndexList)
            {
                if (ix.Type == IndexType.Normal)
                {
                    sb.Append(";CREATE INDEX");
                }
                if (ix.Type == IndexType.Unique)
                {
                    sb.Append(";CREATE UNIQUE INDEX");
                }
                sb.Append($" {name}_{ix.Name} ON {name} ({ix.Columns})");
            }
            return sb.ToString();
        }

        public override void OptimizeTable(string name, bool final = false, bool deduplicate = false)
        {
            throw new NotImplementedException();
        }

        public override void OptimizeTable(string name, string partition, bool final = false, bool deduplicate = false)
        {
            throw new NotImplementedException();
        }

        public override void SetTableComment(string name, string comment)
        {
            throw new NotImplementedException();
        }

        public override void SetTablespace(string table, string tablespace)
        {
            throw new NotImplementedException();
        }

        public override Task SetTablespaceAsync(string table, string tablespace)
        {
            throw new NotImplementedException();
        }

        public override void TruncateTable(string name)
        {
            throw new NotImplementedException();
        }

        public override Task TruncateTableAsync(string name, CancellationToken cancellationToken = default)
        {
            throw new NotImplementedException();
        }

        public override void Vacuum()
        {
            Execute("VACUUM ANALYZE");
        }

        protected override ITable<T> CreateITable<T>(string name)
        {
            return new DuckDbTable<T>(name, this);
        }
    }
}
